﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Linq.Dynamic;
using System.Configuration;


namespace IronOne.SalesOne.DataObjects
{
    public class LinqPolicy
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<Policy> GetPolicies(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<PolicyEntity> query = db.PolicyEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "ContractId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.ContractId);
                            else
                                query = query.OrderByDescending(c => c.ContractId);
                            break;

                    }
                }
                return query.Select(c => Mapper.ToPolicyObject(c)).ToList();
            }
        }


        public Policy GetPolicy(string contractId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToPolicyObject(db.PolicyEntities
                            .SingleOrDefault(p => p.ContractId == contractId));
            }
        }

        public void InsertPolicy(Policy policy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    PolicyEntity entity = Mapper.ToPolicyEntity(policy);
                    db.PolicyEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    policy.ContractId = entity.ContractId;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to insert policy record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert policy ");
                    //throw new Exception("Unable to Insert policy " + policy.PolicyId);
                }
            }
        }

        public int UpdatePolicy(Policy policy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    PolicyEntity entity = Mapper.ToPolicyEntity(policy);
                    db.PolicyEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to update policy record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeletePolicy(Policy policy)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    PolicyEntity entity = Mapper.ToPolicyEntity(policy);
                    db.PolicyEntities.Attach(entity, false);
                    db.PolicyEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to delete policy record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }






        public List<Policy> GetPoliciesForClient(string clientId,string so)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<PolicyEntity> query = db.PolicyEntities;
                query = from pol in db.PolicyEntities
                        where (pol.CustomerID == clientId)  && (pol.SOCode == so)
                        select pol;

                return query.Select(c => Mapper.ToPolicyObject(c)).ToList();
            }
        
        }



        public List<Policy> SearchPolicies(string idNo, string idType, string custName, string customerId, string contactNo, string branchId, string unitId, string soCode)
        {
            StringBuilder sb = new StringBuilder();
            if (!String.IsNullOrEmpty(idNo) && !String.IsNullOrEmpty(idType))
                sb.Append("IDNumber = " + idNo + " AND IDType = " + idType); //sb.Append( "MessageId=5 AND nodeid=3");

            if (!String.IsNullOrEmpty(customerId))
                sb.Append(" AND CustomerID = " + customerId.ToString());
            if (!String.IsNullOrEmpty(contactNo))
                sb.Append(" AND ContactNo = " + contactNo);
            //if (!String.IsNullOrEmpty(branchId) && unitId == "-1" && branchId != "-1")
            //    sb.Append(" AND NodeId=" + branchId.ToString() + " AND UserTypeId = 3");
            //if (!String.IsNullOrEmpty(unitId) && unitId != "-1")
            //    sb.Append(" AND NodeId=" + unitId.ToString() + " AND UserTypeId = 4");
            if (!String.IsNullOrEmpty(soCode) && soCode != "Select")
                sb.Append(" AND SOCode = \"" + soCode + "\"");

            StringBuilder cus = new StringBuilder();
            if (!String.IsNullOrEmpty(custName))
                cus.Append(" AND FirstName = \"" + custName + "\"");




            if (sb.Length > 1 || cus.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (cus.ToString().StartsWith(" AND "))
                    cus.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<PolicyEntity> query = db.PolicyEntities;

                    if (sb.Length > 1 && cus.Length > 1)
                    {
                        query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                join cust in (db.CustomerEntities.Where(cus.ToString()))
                                on pol.CustomerID equals cust.CustomerID
                                select pol;
                    }
                    else if (sb.Length > 1 && cus.Length < 1)
                    {
                        query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                join cust in db.CustomerEntities
                                on pol.CustomerID equals cust.CustomerID
                                select pol;
                    }
                    else if (sb.Length < 1 && cus.Length > 1)
                    {
                        query = from pol in db.PolicyEntities
                                join cust in (db.CustomerEntities.Where(cus.ToString()))
                                on pol.CustomerID equals cust.CustomerID
                                select pol;
                    }


                    //db.PolicyEntities.Where(sb.ToString());
                    return query.Select(c => Mapper.ToPolicyObject(c)).ToList();
                }
            }
            else
                return null;
        }





        public List<Policy> SearchPolicies(ContactSearch polcy,int pageNo)
        {
            StringBuilder sb = new StringBuilder();

            //if (!String.IsNullOrEmpty(polcy.IdNo) && !String.IsNullOrEmpty(polcy.IdType))
            //    sb.Append("IDNumber = " + polcy.IdNo + " AND IDType = " + polcy.IdType);


            StringBuilder str = new StringBuilder();

            //unit
            if (polcy.Unt != null)
            {
                int j = 0;
                foreach (SalesOneUnit un in polcy.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0 )
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }
           


            //branch
            if (polcy.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in polcy.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && polcy.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && polcy.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }
            


            //so
            if (polcy.SOCode != null)
            {
                int i = 0;
                foreach (string so in polcy.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }



            if (!String.IsNullOrEmpty(polcy.Contact))
                sb.Append(" AND ContractId =\"" + polcy.Contact + "\"");
            

            StringBuilder cus = new StringBuilder();
            if (!String.IsNullOrEmpty(polcy.Name))
                cus.Append(" AND FirstName = \"" + polcy.Name + "\"");
            //if (!String.IsNullOrEmpty(polcy.Contact))
            //    cus.Append(" AND ContractNo =\"" + polcy.Contact + "\"");
            if (!String.IsNullOrEmpty(polcy.IdNo) && !String.IsNullOrEmpty(polcy.IdType))
                cus.Append(" AND IDNumber = \"" + polcy.IdNo + "\"" + " AND IDType = " + polcy.IdType);
            if (!String.IsNullOrEmpty(polcy.CliemtId))
                cus.Append(" AND CustomerID = \"" + polcy.CliemtId + "\"");


            if (sb.Length > 1 || cus.Length > 1 || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (cus.ToString().StartsWith(" AND "))
                    cus.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<PolicyEntity> query = db.PolicyEntities;
                    //join u in db.UserEntities on c.SOCode equals u.SOCode
                    if (str.Length >1 )
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities.Where(cus.ToString())                                    
                                    on pol.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on pol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString()) 
                                    on u.UserId equals us.UserId
                                    select pol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities on pol.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on pol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())                                   
                                    on u.UserId equals us.UserId
                                    select pol;

                            //query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                            //        join cust in 
                            //                      (from c in db.CustomerEntities
                            //                      join us in db.UsersInStructureEntities.Where(str.ToString()) 
                            //                      on c.UserID equals us.UserId select c)
                            //        on pol.CustomerID equals cust.CustomerID
                            //        select pol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from pol in db.PolicyEntities
                                    join cust in db.CustomerEntities.Where(cus.ToString())
                                    on pol.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on pol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select pol;


                            //query = from pol in db.PolicyEntities
                            //        join cust in
                            //            (from c in db.CustomerEntities.Where(cus.ToString())
                            //             join us in db.UsersInStructureEntities.Where(str.ToString())
                            //             on c.UserID equals us.UserId
                            //             select c)
                            //        on pol.CustomerID equals cust.CustomerID
                            //        select pol;
                        }
                        else if (sb.Length < 1 && cus.Length < 1)
                        {
                            query = from pol in db.PolicyEntities
                                    join cust in db.CustomerEntities
                                    on pol.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on pol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select pol;


                                    //join cust in
                                    //    (from c in db.CustomerEntities
                                    //     join us in db.UsersInStructureEntities.Where(str.ToString())
                                    //     on c.UserID equals us.UserId
                                    //     select c)
                                    //on pol.CustomerID equals cust.CustomerID
                                    //select pol;
                        }
                    }
                    else if (str.Length <1)
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from pol in db.PolicyEntities
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                    }

                    IQueryable<PolicyEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                    return pageData.Select(c => Mapper.ToPolicyObject(c)).ToList();
                }
            }
            else
                return null;
        }



        public List<Policy> SearchAckSlips(SearchCS ack, int pageNo)
        {
            StringBuilder sb = new StringBuilder();

            if (!String.IsNullOrEmpty(ack.ContractId))
                sb.Append(" AND ContractId = \"" + ack.ContractId + "\"");
            if (!String.IsNullOrEmpty(ack.Status))
                sb.Append(" AND AckStatus = \"" + ack.Status + "\"");

            StringBuilder str = new StringBuilder();

            //unit
            if (ack.Unt != null)
            {
                int j = 0;
                foreach (SalesOneUnit un in ack.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0)
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }



            //branch
            if (ack.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in ack.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && ack.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && ack.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }



            //so
            if (ack.SOCode != null)
            {
                int i = 0;
                foreach (string so in ack.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }


            if (sb.Length > 1  || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<PolicyEntity> query = db.PolicyEntities;

                    //

                    if (sb.Length > 1 && str.Length > 1)
                    {
                         query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                 join ak in db.AcknowledgementSlipEntities
                                 on pol.ContractId equals ak.ContractId                                 
                                 join u in db.UserEntities on ak.SOCode equals u.SOCode
                                 join us in db.UsersInStructureEntities.Where(str.ToString())
                                 on u.UserId equals us.UserId
                                 select pol;


                         //query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                         //        join ak in db.AcknowledgementSlipEntities
                         //        on pol.ContractId equals ak.ContractId
                         //        join cust in db.CustomerEntities
                         //        on pol.CustomerID equals cust.CustomerID
                         //        join u in db.UserEntities on cust.SOCode equals u.SOCode
                         //        join us in db.UsersInStructureEntities.Where(str.ToString())
                         //        on u.UserId equals us.UserId
                         //        select pol;


                         //query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                         //        join ak in db.AcknowledgementSlipEntities
                         //        on pol.ContractId equals ak.ContractId
                         //        join cust in
                         //            (from c in db.CustomerEntities
                         //             join us in db.UsersInStructureEntities.Where(str.ToString())
                         //             on c.UserID equals us.UserId
                         //             select c)
                         //        on pol.CustomerID equals cust.CustomerID
                         //        select pol;

                    }
                    else if (sb.Length > 1 && str.Length < 1)
                    {
                        query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                                join ak in db.AcknowledgementSlipEntities 
                                on pol.ContractId equals ak.ContractId
                                select pol;




                        //query = from pol in (db.PolicyEntities.Where(sb.ToString()))
                        //        join cust in db.CustomerEntities
                        //        on pol.CustomerID equals cust.CustomerID
                        //        select pol;
                    }
                    else if (sb.Length < 1 && str.Length > 1)
                    {
                        query = from pol in db.PolicyEntities
                                join ak in db.AcknowledgementSlipEntities
                                on pol.ContractId equals ak.ContractId                                
                                join u in db.UserEntities on ak.SOCode equals u.SOCode
                                join us in db.UsersInStructureEntities.Where(str.ToString())
                                on u.UserId equals us.UserId
                                select pol;

                        //query = from pol in db.PolicyEntities
                        //        join ak in db.AcknowledgementSlipEntities
                        //        on pol.ContractId equals ak.ContractId
                        //        join cust in db.CustomerEntities
                        //        on pol.CustomerID equals cust.CustomerID
                        //        join u in db.UserEntities on cust.SOCode equals u.SOCode
                        //        join us in db.UsersInStructureEntities.Where(str.ToString())
                        //        on u.UserId equals us.UserId
                        //        select pol;

                        //query = from pol in db.PolicyEntities
                        //        join ak in db.AcknowledgementSlipEntities
                        //        on pol.ContractId equals ak.ContractId
                        //        join cust in
                        //            (from c in db.CustomerEntities
                        //             join us in db.UsersInStructureEntities.Where(str.ToString())
                        //             on c.UserID equals us.UserId
                        //             select c)
                        //        on pol.CustomerID equals cust.CustomerID
                        //        select pol;
                    }
                    //

                    IQueryable<PolicyEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                    return pageData.Select(c => Mapper.ToPolicyObject(c)).ToList();
                }
            }
            else
                return null;
        }



        public List<PendingProposal> SearchPendProps(SearchCS pep, int pageNo)
        {
            StringBuilder sb = new StringBuilder();

            if (!String.IsNullOrEmpty(pep.ContractId))
                sb.Append(" AND ContractId = \"" + pep.ContractId + "\"");


            StringBuilder str = new StringBuilder();

            //unit
            if (pep.Unt != null)
            {
                int j = 0;
                foreach (SalesOneUnit un in pep.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0)
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }



            //branch
            if (pep.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in pep.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && pep.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && pep.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }



            //so
            if (pep.SOCode != null)
            {
                int i = 0;
                foreach (string so in pep.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }



            StringBuilder cus = new StringBuilder();
            if (!String.IsNullOrEmpty(pep.Status))
                sb.Append(" AND Status = \"" + pep.Status + "\"");



            if (sb.Length > 1 || cus.Length > 1 || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (cus.ToString().StartsWith(" AND "))
                    cus.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<PendingProposalEntity> query = db.PendingProposalEntities;

                    if (str.Length > 1)
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities.Where(cus.ToString())
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on penPol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select penPol;

                                        

                            //query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                            //        join cust in
                            //            (from c in db.CustomerEntities.Where(cus.ToString())
                            //             join us in db.UsersInStructureEntities.Where(str.ToString())
                            //             on c.UserID equals us.UserId
                            //             select c)
                            //        on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                            //        select penPol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on penPol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select penPol;

                            //query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                            //        join cust in
                            //            (from c in db.CustomerEntities
                            //             join us in db.UsersInStructureEntities.Where(str.ToString())
                            //             on c.UserID equals us.UserId
                            //             select c)
                            //        on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                            //        select penPol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from penPol in db.PendingProposalEntities
                                    join cust in db.CustomerEntities.Where(cus.ToString())
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on penPol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select penPol;

                            //query = from penPol in db.PendingProposalEntities
                            //        join cust in
                            //            (from c in db.CustomerEntities.Where(cus.ToString())
                            //             join us in db.UsersInStructureEntities.Where(str.ToString())
                            //             on c.UserID equals us.UserId
                            //             select c)
                            //        on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                            //        select penPol;
                        }
                        else if (sb.Length < 1 && cus.Length < 1)
                        {
                            query = from penPol in db.PendingProposalEntities
                                    join cust in db.CustomerEntities
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    join u in db.UserEntities on penPol.SOCode equals u.SOCode
                                    join us in db.UsersInStructureEntities.Where(str.ToString())
                                    on u.UserId equals us.UserId
                                    select penPol;


                            //query = from penPol in db.PendingProposalEntities
                            //        join cust in
                            //            (from c in db.CustomerEntities
                            //             join us in db.UsersInStructureEntities.Where(str.ToString())
                            //             on c.UserID equals us.UserId
                            //             select c)
                            //        on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                            //        select penPol;
                        }
                    }
                    else if (str.Length < 1)
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    select penPol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    select penPol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from penPol in (db.PendingProposalEntities.Where(sb.ToString()))
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on penPol.PolicyEntity.CustomerID equals cust.CustomerID
                                    select penPol;
                        }
                    }
                    //

                    IQueryable<PendingProposalEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                    return pageData.Select(c => Mapper.ToPendingProposalObject(c)).ToList();



                }
            }
            else
                return null;
        }



        public List<Claim> searchClaim(SearchCS clm, int pageNo)
        {
            StringBuilder sb = new StringBuilder();

            if (!String.IsNullOrEmpty(clm.ContractId))
                sb.Append(" AND ContractNo = \"" + clm.ContractId + "\"");
            if (!String.IsNullOrEmpty(clm.ClientId))
                sb.Append(" AND CustomerId = \"" + clm.ClientId + "\"");
            if (!String.IsNullOrEmpty(clm.Status))
                sb.Append(" AND ClaimStatus = " + int.Parse(clm.Status));
            if (!String.IsNullOrEmpty(clm.ClaimNo))
                sb.Append(" AND ClaimNo = \"" + clm.ClaimNo + "\"");
            
            

            StringBuilder str = new StringBuilder();

            //unit
            if (clm.Unt != null)
            {
                int j = 0;
                foreach (SalesOneUnit un in clm.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0)
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }



            //branch
            if (clm.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in clm.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && clm.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && clm.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }



            //so
            if (clm.SOCode != null)
            {
                int i = 0;
                foreach (string so in clm.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }


            if (sb.Length > 1 || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<ClaimEntity> query = db.ClaimEntities;




                    if (sb.Length > 1 && str.Length > 1)
                    {
                        query = from claim in (db.ClaimEntities.Where(sb.ToString()))
                                join u in db.UserEntities on claim.SOCode equals u.SOCode
                                join us in db.UsersInStructureEntities.Where(str.ToString())
                                on u.UserId equals us.UserId
                                select claim;


                        //query = from claim in (db.ClaimEntities.Where(sb.ToString()))
                        //        join cust in db.CustomerEntities
                        //        on claim.CustomerId equals cust.CustomerID
                        //        join u in db.UserEntities on cust.SOCode equals u.SOCode
                        //        join us in db.UsersInStructureEntities.Where(str.ToString())
                        //        on u.UserId equals us.UserId
                        //        select claim;
                        

                    }
                    else if (sb.Length > 1 && str.Length < 1)
                    {
                        query = from claim in (db.ClaimEntities.Where(sb.ToString()))
                                select claim;
                    }
                    else if (sb.Length < 1 && str.Length > 1)
                    {
                        query = from claim in db.ClaimEntities
                                join u in db.UserEntities on claim.SOCode equals u.SOCode
                                join us in db.UsersInStructureEntities.Where(str.ToString())
                                on u.UserId equals us.UserId
                                select claim;




                        //query = from claim in db.ClaimEntities
                        //        join cust in db.CustomerEntities
                        //        on claim.CustomerId equals cust.CustomerID
                        //        join u in db.UserEntities on cust.SOCode equals u.SOCode
                        //        join us in db.UsersInStructureEntities.Where(str.ToString())
                        //        on u.UserId equals us.UserId
                        //        select claim;
                    }



                    IQueryable<ClaimEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);


                    return pageData.Select(c => Mapper.ToClaimObject(c)).ToList();
                }
            }
            else
                return null;
        }



        public List<Complaint> SearchComplaint(SearchCS comp)
        {
            StringBuilder sb = new StringBuilder();
            //complaint table

            if (!String.IsNullOrEmpty(comp.ContractId))
                sb.Append(" AND ContractNo = \"" + comp.ContractId + "\"");
            if (!String.IsNullOrEmpty(comp.ClientId))
                sb.Append(" AND CustomerId = \"" + comp.ClientId + "\"");
            if (!String.IsNullOrEmpty(comp.Status))
                sb.Append(" AND ComplaintStatus = " + int.Parse(comp.Status));
            if (!String.IsNullOrEmpty(comp.ClaimNo))
                sb.Append(" AND ComplaintId = \"" + comp.ClaimNo + "\"");
            
            

            StringBuilder str = new StringBuilder();

            //unit
            if (comp.Unt != null)
            {
                int j = 0;
                foreach (SalesOneUnit un in comp.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0)
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }



            //branch
            if (comp.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in comp.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && comp.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && comp.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }



            //so
            if (comp.SOCode != null)
            {
                int i = 0;
                foreach (string so in comp.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }


            if (sb.Length > 1 || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<ComplaintEntity> query = db.ComplaintEntities;

                    

                    if (sb.Length > 1 && str.Length > 1)
                    {
                        query = from complaint in (db.ComplaintEntities.Where(sb.ToString()))
                                join cust in db.CustomerEntities
                                on complaint.CustomerId equals cust.CustomerID
                                join u in db.UserEntities on cust.SOCode equals u.SOCode
                                join us in db.UsersInStructureEntities.Where(str.ToString())
                                on u.UserId equals us.UserId
                                select complaint;

                        //query = from complaint in (db.ComplaintEntities.Where(sb.ToString()))
                        //        join cust in
                        //            (from c in db.CustomerEntities
                        //             join us in db.UsersInStructureEntities.Where(str.ToString())
                        //             on c.UserID equals us.UserId
                        //             select c)
                        //        on complaint.CustomerId equals cust.CustomerID
                        //        select complaint;

                    }
                    else if (sb.Length > 1 && str.Length < 1)
                    {
                        query = from complaint in (db.ComplaintEntities.Where(sb.ToString()))
                                join cust in db.CustomerEntities
                                on complaint.CustomerId equals cust.CustomerID
                                select complaint;
                    }
                    else if (sb.Length < 1 && str.Length > 1)
                    {
                        query = from complaint in db.ComplaintEntities
                                join cust in db.CustomerEntities
                                on complaint.CustomerId equals cust.CustomerID
                                join u in db.UserEntities on cust.SOCode equals u.SOCode
                                join us in db.UsersInStructureEntities.Where(str.ToString())
                                on u.UserId equals us.UserId
                                select complaint;


                        //query = from complaint in db.ComplaintEntities
                        //        join cust in
                        //            (from c in db.CustomerEntities
                        //             join us in db.UsersInStructureEntities.Where(str.ToString())
                        //             on c.UserID equals us.UserId
                        //             select c)
                        //        on complaint.CustomerId equals cust.CustomerID
                        //        select complaint;
                    }
                    

                    return query.Select(c => Mapper.ToComplaintObject(c)).ToList();
                }
            }
            else
                return null;
        }





        public List<Policy> GetPoliciesForDate(DateTime fromDate, DateTime toDate, string soCode)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<PolicyEntity> query = db.PolicyEntities;

                query = from p
                        in db.PolicyEntities
                        where ((p.DueDate > fromDate) && (p.DueDate < toDate) && (p.SOCode == soCode))
                        select p;


                //IQueryable<PolicyEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                return query.Select(c => Mapper.ToPolicyObject(c)).ToList();

            }
        }


        public List<Policy> GetPoliciesForDate(DateTime fromDate, DateTime toDate, string soCode,int pageNo)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<PolicyEntity> query = db.PolicyEntities;

                query = from p
                        in db.PolicyEntities
                        where ((p.DueDate > fromDate) && (p.DueDate < toDate) && (p.SOCode == soCode))
                        select p;


                IQueryable<PolicyEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                return pageData.Select(c => Mapper.ToPolicyObject(c)).ToList();

            }
        }


    }

}